﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class PayController
    {
        private DBConnector connector;
        public PayController()
        {
            connector = new DBConnector();     
        }

        public double getSalary(string firstName, string lastName)
        {
            string query1 = "SELECT salary, payingBasis FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\';";
            string query2 = "SELECT periodStart, periodEnd from period WHERE \'" + DateTime.Now.ToString("yyyy-MM-dd") + "\' >= periodStart and \'" + DateTime.Now.ToString("yyyy-MM-dd") + "\' <= periodEnd;";
            double employeeSalary, totalSalary;
            TimeSpan span;
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query1, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                MySqlDataAdapter dataAdapter1 = new MySqlDataAdapter(query2, connector.getConnection());
                MySqlCommandBuilder commandBuilder1 = new MySqlCommandBuilder(dataAdapter1);
                DataTable dataTable1 = new DataTable();
                dataAdapter1.Fill(dataTable1);
                connector.closeConnection();
                employeeSalary = double.Parse(dataTable.Rows[0][0].ToString());
                span = DateTime.Parse(dataTable1.Rows[0][1].ToString())-DateTime.Parse(dataTable1.Rows[0][0].ToString());
                if(dataTable.Rows[0][1].ToString() == "Daily")
                    totalSalary = span.TotalDays * employeeSalary;
                else
                    totalSalary = (span.TotalDays/28) * employeeSalary;
                return totalSalary;
            }
            else
            {
                return 0;
            }
        }

        public double getTip(string firstName, string lastName)
        {
            string query = "SELECT totalTip FROM tipreport WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";                       
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return double.Parse(dataTable.Rows[0][0].ToString());
            }
            else
            {
                return 0;
            }
        }

        public double getDebt(string firstName, string lastName)
        {
            double debt = 0;
            string query = "SELECT debt FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";                       
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    debt = double.Parse(dataReader[0]+"");
                dataReader.Close();
                connector.closeConnection();
            }
            return debt;
        }

        public double getCharges(string firstName, string lastName)
        {
            MiscChargeController miscCharge = new MiscChargeController();
            string query = "SELECT SUM(chargedAmount) FROM salarycharges WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\') AND periodID = "+miscCharge.getPeriodID(); 
            double charges=0;
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    String retrieved = dataReader[0] + "";
                    if (retrieved.Equals(""))
                    {
                        charges = 0;
                    }
                    else
                        charges = Double.Parse(dataReader[0] + "");
                }
                dataReader.Close();
                connector.closeConnection();
            }
            return charges;
        }

        public double getAbsences(string firstName, string lastName)
        {
            double absences = 0;
            MiscChargeController miscCharge = new MiscChargeController();
            string query = "SELECT absences FROM attendance WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\') and periodID = "+miscCharge.getPeriodID();
            string query1 = "SELECT salary FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    absences = double.Parse(dataReader[0] + "");
                dataReader.Close();
                cmd = new MySqlCommand(query1, connector.getConnection());
                dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    absences = absences*double.Parse(dataReader[0] + "");
                dataReader.Close();
                connector.closeConnection();
            }
            return absences;
        }

        public double getLate(string firstName, string lastName)
        {
            MiscChargeController miscCharge = new MiscChargeController();
            EmployeeController empC = new EmployeeController();
            string query = "SELECT late FROM attendance WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\') and periodID = " + miscCharge.getPeriodID();
            string query1 = "SELECT sum(charge) FROM semilate WHERE employeeCode = "+empC.getEmployeeCode(firstName,lastName)+" AND periodID = " + miscCharge.getPeriodID();
            string query2 = "SELECT salary FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";
            double charges=0;
            double salary=0;
            int late = 0;
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query1, connector.getConnection());
                    MySqlDataReader dataReader = cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        String retrieved = dataReader[0] + "";
                        if(retrieved.Equals(""))
                        {
                            charges = 0;
                        }
                        else
                            charges = Double.Parse(dataReader[0]+"");
                    }
                    dataReader.Close();
                    MySqlCommand cmd1 = new MySqlCommand(query2, connector.getConnection());
                    MySqlDataReader dataReader1 = cmd1.ExecuteReader();
                    if (dataReader1.Read())
                        salary = (double)dataReader1["salary"];
                    dataReader1.Close();
                    MySqlCommand cmd2 = new MySqlCommand(query, connector.getConnection());
                    MySqlDataReader dataReader2 = cmd2.ExecuteReader();
                    if (dataReader2.Read())
                        late = (int)dataReader2["late"];
                    dataReader2.Close();
                    connector.closeConnection();
                    if (salary >= 300)
                        return charges + (double)(late / 2);
                    else
                        return charges + (double)(late / 3);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }

        public double getBonuses(string firstName, string lastName)
        {
            double bonuses = 0;
            string query = "SELECT bonusesDeductions FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";                       
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    bonuses = double.Parse(dataReader[0] + "");
                dataReader.Close();
                connector.closeConnection();
            }
            return bonuses;
        }

        public double getSSS(string firstName, string lastName)
        {
            double sss = 0;
            string query = "SELECT SSS FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";                       
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    sss = double.Parse(dataReader[0] + "");
                dataReader.Close();
                connector.closeConnection();
            }
            return sss;
        }

        public double getPhilHealth(string firstName, string lastName)
        {
            double phealth = 0;
            string query = "SELECT PhilHealth FROM employee WHERE employeeCode = (SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\');";                       
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                    phealth = double.Parse(dataReader[0] + "");
                dataReader.Close();
                connector.closeConnection();
            }
            return phealth;
        }

        public double getPay(string firstName, string lastName)
        {
            return getSalary(firstName, lastName) + getTip(firstName, lastName) + getBonuses(firstName, lastName) - getCharges(firstName, lastName) - getAbsences(firstName, lastName) - getLate(firstName, lastName) - getDebt(firstName, lastName) - getSSS(firstName, lastName) - getPhilHealth(firstName, lastName);
        }

        public Pay getData(string firstName, string lastName)
        {
            Pay payData = new Pay();
            payData.firstName = firstName;
            payData.lastName = lastName;
            payData.salary = getSalary(firstName, lastName);
            payData.tip = getTip(firstName, lastName); 
            payData.charges = getCharges(firstName, lastName);
            payData.absences = getAbsences(firstName, lastName);
            payData.late = getLate(firstName, lastName);
            payData.debt = getDebt(firstName, lastName); 
            payData.SSS = getSSS(firstName, lastName);
            payData.PhilHealth = getPhilHealth(firstName, lastName);
            payData.TotalPay = getPay(firstName, lastName);
            if (getBonuses(firstName, lastName) >= 0)
            {
                payData.bonuses = getBonuses(firstName, lastName);
                payData.deductions = 0;
            }
            else
            {
                payData.deductions = getBonuses(firstName, lastName) * -1;
                payData.bonuses = 0;
            }
                return payData;
        }

        public bool addSalaryReport(string firstName, string lastName)
        {
            string query ="";
            MiscChargeController miscChargeControl = new MiscChargeController();
            if (getBonuses(firstName, lastName) >= 0)
                query = "INSERT INTO salaryreport (employeeCode, periodID, salary, tip, debt, charges, absences, late, bonuses, deductions, SSS, PhilHealth, pay) VALUES ((SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\'), "
                    + miscChargeControl.getPeriodID() + ", " + getSalary(firstName, lastName) + ", " + getTip(firstName, lastName) + ", " + getDebt(firstName, lastName) + ", " + getCharges(firstName, lastName) + ", " + getAbsences(firstName, lastName) + ", "
                    + getLate(firstName, lastName) + ", " + getBonuses(firstName, lastName) + ",  0, " + getSSS(firstName, lastName) + ", " + getPhilHealth(firstName, lastName) + ", " + getPay(firstName, lastName) + ")";
            else
            {
                double a = getBonuses(firstName,lastName);
                a = a * -1;
                query = "INSERT INTO salaryreport (employeeCode, periodID, salary, tip, debt, charges, absences, late, bonuses, deductions, SSS, PhilHealth, pay) VALUES ((SELECT employeeCode FROM employee WHERE firstName = \'" + firstName + "\' and lastName = \'" + lastName + "\'), "
                    + miscChargeControl.getPeriodID() + ", " + getSalary(firstName, lastName) + ", " + getTip(firstName, lastName) + ", " + getDebt(firstName, lastName) + ", " + getCharges(firstName, lastName) + ", " + getAbsences(firstName, lastName) + ", "
                    + getLate(firstName, lastName) + ", 0,"+ a + ", " + getSSS(firstName, lastName) + ", " + getPhilHealth(firstName, lastName) + ", " + getPay(firstName, lastName) + ")";
            }
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (Exception ex)
                {
                    //MessageBox.Show("An error has occurred in adding the salary report to the database.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    MessageBox.Show(ex.Message+"\n"+ex.StackTrace);
                    return false;
                }
            }
            return false;
        }

        public Pay getPreviousReports(int employeeCode , int periodID)
        {
            Pay pay = new Pay();
            string query = "SELECT * from salaryreport where employeeCode = " + employeeCode + " AND periodID = " + periodID;
            if (connector.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    pay.salary = (double)dataReader["salary"];
                    pay.tip = (double)dataReader["tip"];
                    pay.debt = (double)dataReader["debt"];
                    pay.charges = (double)dataReader["charges"];
                    pay.absences = (double)dataReader["absences"];
                    pay.late = (double)dataReader["late"];
                    pay.bonuses = (double)dataReader["bonuses"];
                    pay.deductions = (double)dataReader["deductions"];
                    pay.SSS = (double)dataReader["SSS"];
                    pay.PhilHealth = (double)dataReader["PhilHealth"];
                    pay.TotalPay = (double)dataReader["pay"];
                }
                dataReader.Close();
                connector.closeConnection();
            }
            return pay;
        }
    }
}
